set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=200;
set hive.exec.max.dynamic.partitions.pernode=200;

insert overwrite table bidefault.dm_waybill_collect_intime_diagnostic_summary partition (dt)
select
     waybill.taking_date
    ,waybill.receive_network_code
    ,waybill.receive_network_name
    ,waybill.receive_franchisee_code
    ,waybill.receive_franchisee
    ,waybill.receive_proxy_code
    ,waybill.receive_proxy
    ,waybill.actual_collect_code         as collect_code
    ,waybill.actual_collect_name         as collect_name
    ,waybill.actual_center_code          as center_code
    ,waybill.actual_center_name          as center_name
    ,waybill.actual_network_shipment_no
    ,waybill.taking_user_code
    ,waybill.taking_user_name
    ,waybill.customer_code
    ,waybill.customer_name
    ,waybill.type       -- 是否应交件
    ,waybill.is_over    -- 是否准点
    ,count(case when waybill.is_over is not null and waybill.type = 0 then waybill end)                          as should_hand_over_num                      -- 应交件票数
    ,count(case when waybill.is_over = 1         and waybill.type = 0 then waybill end)                          as hand_over_in_time_num                     -- 准点交件票数
    ,count(case when waybill.is_over <> 1        and waybill.type = 0 then waybill end)                          as hand_over_not_in_time_num                 -- 不准点交件票数
    ,count(case when waybill.is_over = 0 and waybill.type = 0 then waybill end)                                  as hand_over_network_resp_num                -- 交接网点责任票数
    ,count(case when waybill.is_over = 2 and waybill.type = 0 then waybill end)                                  as hand_over_collect_resp_num                -- 交接集散责任票数
    ,count(case when waybill.is_over is not null and waybill.type = 0 and waybill.is_arr_collect_intime = 0 then waybill end) as collect_arr_late_cnt         -- 到集散不准点
    ,count(case when waybill.is_over <> 1        and waybill.type = 0 then waybill end)                          as center_arr_late_cnt                       -- 到中心不准点（现在和不准点交件票数逻辑相同）
    ,count(case when waybill.type = 0 and waybill.is_interrupted = 1  then waybill end)                                               as interrupted_taking_cnt                    -- 截单后揽收量
    ,count(case when coalesce(waybill.actual_send_time, waybill.actual_collect_arr_time,
                              waybill.first_nodal_send_time , waybill.actual_center_arr_time, waybill.first_center_send_time)
                     is null then 1 end)                                                                         as taking_no_send_cnt                        -- 有揽未发量
    ,count(case when waybill.type = 0 and
                    nvl(actual_send_time, date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00')
                    > first_network_plan_send_time
                    then waybill
            end)                                                                                                 as network_send_late_cnt                     -- 网点发车晚量
    ,count(case when waybill.type = 0 and
                     actual_collect_code is not null
                        and nvl(actual_collect_arr_time, date_add('{{ execution_date | cst_ds }}', 1)|| ' 07:00:00') > plan_collect_arr_time
                     then waybill
           end)                                                                                                  as network_collect_transport_late_cnt        -- 网点集散运输晚数量

    ,count(case when waybill.type = 0 and
                     actual_collect_code is null
                     and nvl(actual_center_arr_time, date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00') > plan_center_arr_time
                     then waybill
           end)                                                                                                  as network_center_transport_late_cnt         -- 网点中心运输晚数量
    ,count(case when waybill.type = 0 and
                     actual_collect_code is not null
                     and nvl(first_nodal_send_time,  date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00')
                     > first_nodal_plan_send_time
                     then waybill
           end)                                                                                                  as collect_send_late_cnt                     -- 集散发车晚数量
    ,count(case when waybill.type = 0 and
                     actual_collect_code is not null
                     and nvl(actual_center_arr_time, date_add('{{ execution_date | cst_ds }}', 1) || ' 07:00:00')
                     > plan_center_arr_time
                     then waybill
          end)                                                                                                   as collect_center_transport_late_cnt         --集散中心运输晚数量
    ,count(case when if_center_send_timely = 1 then waybill end)                                                 as center_send_timely_cnt                    --交件晚点及时发出量
    ,count(case when if_center_send_timely = 0 then waybill end)                                                 as center_send_late_cnt                      --交件晚点未及时发出量
    ,nvl(count(false_taking.waybill_no),0)                                                                       as false_taking_cnt                          --虚假揽收数量
    ,waybill.area_code
    ,waybill.area_name
    ,dim_network.zone_code
    ,dim_network.zone_name
    ,count(actual_collect_time)                                                                                  as network_taking_cnt     -- 网点揽收数量
    ,count(actual_send_time)                                                                                     as network_send_cnt       -- 网点发件数量
    ,count(actual_collect_arr_time)                                                                              as collect_arr_cnt        -- 集散到件数量
    ,count(first_nodal_send_time)                                                                                as collect_send_cnt       -- 集散到件数量
    ,count(actual_center_arr_time)                                                                               as center_arr_cnt         -- 中心到件数量
    ,count(first_center_send_time)                                                                               as center_send_cnt        -- 中心发件数量
    ,waybill.order_source
    ,waybill.dt
from jms_dm.dm_waybill_collect_intime waybill
left join
        jms_dm.dm_waybill_false_taking_dt false_taking
            on waybill.waybill = false_taking.waybill_no
               and false_taking.dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
left join
        jms_dim.dim_network_whole_massage dim_network
            on waybill.receive_network_code = dim_network.code
where waybill.dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
group by
     waybill.receive_network_code
    ,waybill.receive_network_name
    ,waybill.receive_proxy_code
    ,waybill.receive_proxy
    ,waybill.receive_franchisee_code
    ,waybill.receive_franchisee
    ,waybill.actual_collect_code
    ,waybill.actual_collect_name
    ,waybill.actual_center_code
    ,waybill.actual_center_name
    ,waybill.actual_network_shipment_no
    ,waybill.taking_user_code
    ,waybill.taking_user_name
    ,waybill.customer_code
    ,waybill.customer_name
    ,waybill.taking_date
    ,waybill.area_code
    ,waybill.area_name
    ,waybill.type
    ,waybill.is_over
    ,dim_network.zone_code
    ,dim_network.zone_name
    ,waybill.order_source
    ,waybill.dt
distribute by waybill.dt, pmod(hash(rand()), 5)
;
